﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using Shrestha.Data.EventModel;
using Microsoft.Maps.MapControl.WPF;
using System.Windows;
using System.IO;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;


namespace Shrestha.Data.Database
{
    public class MySqlManager: IDbManager
    {

        public object GetConnection(String DbHost, String DbName, String DbUser, String DbPassword)
        {
            string strProvider = "Data Source=" + DbHost + ";Database=" + DbName + ";User ID=" + DbUser + ";Password=" + DbPassword;
            return new MySqlConnection(strProvider);
        }

        public List<string> GetTableNames(object ConnectionObject)
        {
            List<string> tableList = new List<string>();
            try
            {
                MySqlConnection myConn = ConnectionObject as MySqlConnection;
                myConn.Open();
                MySqlCommand cmd = new MySqlCommand("show Tables;", myConn);
                MySqlDataReader mdr = cmd.ExecuteReader();

                while (mdr.Read()) tableList.Add(mdr.GetString(0));
                mdr.Close();
                myConn.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            tableList.Sort();
            return tableList;
        }

        public List<string> GetColumnNames(object ConnectionObject, object SelectedTable)
        {
            List<string> columnList = new List<string>();
            try
            {
                MySqlConnection myConn = ConnectionObject as MySqlConnection;
                myConn.Open();
                MySqlCommand cmd = new MySqlCommand("describe " + (SelectedTable as string) + ";", myConn);
                MySqlDataReader mdr = cmd.ExecuteReader();
                while (mdr.Read()) columnList.Add(mdr.GetString(0));
                mdr.Close();
                myConn.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            columnList.Sort();
            return columnList;
        }

        public TriEventCollection Execute3Query(String query, object ConnectionObject)
        {
            TriEventCollection td = new TriEventCollection();
            try
            {
                //List<IEvent> teList = new List<IEvent>();
                MySqlConnection conn = ConnectionObject as MySqlConnection;
                if (!(conn.State == System.Data.ConnectionState.Open)) conn.Open();
                MySqlCommand cmd = new MySqlCommand(query, conn);

                MySqlDataReader mdr = cmd.ExecuteReader();
                while (mdr.Read())
                {
                    try
                    {
                        Location el = new Location(mdr.GetDouble(0), mdr.GetDouble(1));
                        String ed = mdr.GetString(2);
                        TriEvent te = new TriEvent(el, ed);
                        td.TriEvents.Add(te);

                        //TriEvent te = new TriEvent(mdr.GetDouble(0), mdr.GetDouble(1), DateTime.Parse(mdr.GetDateTime(2).ToString()));
                        //teList.Add(te);
                    }
                    catch (Exception e)
                    {
                        throw e;
                    }
                }
                conn.Close();
            }
            catch(Exception e)
            {
                throw e;
            }
            return td;

            //return teList;
        }

        public TriEventMetaCollection ExecuteMetaQuery(String query, object ConnectionObject)
        {
            TriEventMetaCollection td = new TriEventMetaCollection();
            try
            {
                MySqlConnection conn = ConnectionObject as MySqlConnection;
                if (!(conn.State == System.Data.ConnectionState.Open)) conn.Open();
                MySqlCommand cmd = new MySqlCommand(query, conn);

                MySqlDataReader mdr = cmd.ExecuteReader();
                while (mdr.Read())
                {
                    try
                    {
                        TriEventMeta tme = new TriEventMeta(mdr.GetDateTime(0), mdr.GetString(1));
                        td.MetaList.Add(tme);
                    }
                    catch (Exception e)
                    {
                        throw e;
                    }
                }
                conn.Close();
            }
            catch (Exception e)
            {
                throw e;
            }
            return td;

            //return teList;
        }
    }
}


